import pandas as pd
import json
from typing import List
import copy
from itertools import zip_longest

# 最终xlsx文件的字段
word_header = ["word_id", "word", "frequency_name", "chinese", "example", "translation", "notes"]


def write_data_to_xlsx(data: List, columns, file_name):
    """
    写入文件
    :param data:
    :param columns:
    :param file_name:
    :return:
    """
    pd.DataFrame(data, columns=columns).to_excel(r'./parse_phrase/{0}.xlsx'.format(file_name),
                                                 sheet_name='Sheet1',
                                                 index=False,
                                                 header=True
                                                 )


def parse_paraphrase(paraphrase):
    paraphrase_list = []
    for p in paraphrase:
        frequency_name = p.get('frequency_name')
        chinese = p.get('chinese')
        gy_example = p.get('gy_example')
        gy_notes = p.get('gy_notes')
        dic = {"frequency_name": frequency_name, "chinese": chinese}
        items = []
        for (gy_exam, gy_note) in zip_longest(gy_example, gy_notes):
            new_dic = copy.deepcopy(dic)
            if gy_exam:
                example = gy_exam.get('english') + '[E{0}-{1}]'.format(len(gy_example), gy_example.index(gy_exam) + 1)
                translation = gy_exam.get('chinese')
            else:
                example = ''
                translation = ''
            if gy_note:
                notes = gy_note.get('notes')
            else:
                notes = ''
            new_dic.update({"example": example, "translation": translation, "notes": notes})
            items.append(new_dic)
        paraphrase_list.extend(items)
    return paraphrase_list


def parse_xlsx():
    df = pd.read_excel(r'./split_table_data/phrase.xlsx', sheet_name='Sheet1', header=None)  # sheet_name 读取那个工作表
    data = df.values  # 全部数据
    header = data[0]  # 读取第一行的列名
    other_lines = data[1:]  # 除第一行外剩余数据
    result = [dict(zip(header, line)) for line in other_lines]
    xlsx_data = []  # 所有解析数据
    for i in result:
        word_id = i.get('id')
        word = i.get('word')

        detail_json = i.get('detail_json')
        if not isinstance(detail_json, float):  # 存在多条无内容数据 如:china
            json_data = json.loads(i.get('detail_json'))
            # if word == 'make it':
            gy_paraphrase = json_data.get('gy_paraphrase')
            paraphrase_list = parse_paraphrase(gy_paraphrase)
            for par in paraphrase_list:
                frequency_name = par.get('frequency_name')
                chinese = par.get('chinese')
                example = par.get('example')
                translation = par.get('translation')
                notes = par.get('notes')
                xlsx_data.append([
                    word_id, word, frequency_name, chinese, example, translation, notes
                ])
    write_data_to_xlsx(xlsx_data, word_header, 'parse_phrase')


if __name__ == '__main__':
    parse_xlsx()
